Joins in sql server
Joins in SQL server are used to query (retrieve) data from 2 or more related tables. In general tables are related to each other using foreign key constraints.In SQL server, there are different types of JOINS.
1. CROSS JOIN
2. INNER JOIN
3. OUTER JOIN
Outer Joins are again divided into 3 types
1. Left Join or Left Outer Join
2. Right Join or Right Outer Join
3. Full Join or Full Outer Join
Now let's understand all the JOIN types, with examples and the differences between them.
Tables used during session :
CREATE TABLE tblDepartmentHead
(
Id INT PRIMARY KEY IDENTITY,
Name VARCHAR(50),
Experience INT
)
INSERT INTO tblDepartmentHead VALUES('Rick',10)
INSERT INTO tblDepartmentHead VALUES('Ron',11)
INSERT INTO tblDepartmentHead VALUES('Christie',15)
INSERT INTO tblDepartmentHead VALUES('Cindrella',12)
INSERT INTO tblDepartmentHead VALUES('Shhon',9)
CREATE TABLE tblDepartment
(
Id INT PRIMARY KEY IDENTITY,
DepartmentName VARCHAR(50),
DepartmentLocation VARCHAR(100),
DepartmentHeadId INT Constraint FK_tblDepartment_DepartmentHeadId FOREIGN KEY REFERENCES tblDepartmentHead(Id)
)
INSERT INTO tblDepartment VALUES('IT','London',1)
INSERT INTO tblDepartment VALUES('PAYROLL','Delhi',2)
INSERT INTO tblDepartment VALUES('HR','New York',3)
INSERT INTO tblDepartment VALUES('Other Department','Sydney',4)
Create Table tblGender
(
ID int Primary Key IDENTITY,
Gender nvarchar(50)
)
INSERT INTO tblGender VALUES('Male')
INSERT INTO tblGender VALUES('Female')
INSERT INTO tblGender VALUES('Unknown')
CREATE TABLE tblEmployee
(
ID INT PRIMARY KEY IDENTITY,
Name VARCHAR(50),
GenderId INT CONSTRAINT FK_tblEmployee_GenderId FOREIGN KEY REFERENCES tblGender(ID),
Salary INT,
DepartmentId INT CONSTRAINT FK_tblEmployee_DepartmentId FOREIGN KEY REFERENCES tblDepartment(Id)
)
INSERT INTO tblEmployee VALUES('Tom', 1, 4000, 1)
INSERT INTO tblEmployee VALUES('Pam', 2, 2000, 3)
INSERT INTO tblEmployee VALUES('John', 1, 3500, 1)
INSERT INTO tblEmployee VALUES('Sam', 1, 2000, 2)
INSERT INTO tblEmployee VALUES('Todd', 1, 5000, 2)
INSERT INTO tblEmployee VALUES('Ben', 1, 5200, 1)
INSERT INTO tblEmployee VALUES('Sara', 2, 2000, 3)
INSERT INTO tblEmployee VALUES('Valaria', 2, 3000, 1)
INSERT INTO tblEmployee VALUES('James', 1, 3200, NULL)
INSERT INTO tblEmployee VALUES('Russell', NULL, 4000, NULL)Employee Table (tblEmployee)
Departments Table (tblDepartment)
General Formula for Joins
SELECT ColumnListFROM LeftTableName
JOIN_TYPE RightTableNameON JoinCondition
JOIN_TYPE RightTableNameON JoinCondition
CROSS JOIN
CROSS JOIN, produces the cartesian product of the 2 tables involved in the join. For example, in the Employees table we have 10 rows and in the Departments table we have 4 rows. So, a cross join between these 2 tables produces 40 rows. Cross Join shouldn't have ON clause.
CROSS JOIN Query:
SELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeCROSS JOIN tblDepartment
JOIN or INNER JOIN
Write a query, to retrieve Name, Gender, Salary and DepartmentName from Employees and Departments table. The output of the query should be as shown below.
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeJOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
Note: JOIN or INNER JOIN means the same. It's always better to use INNER JOIN, as this explicitly specifies your intention.
If you look at the output, we got only 8 rows, but in the Employees table, we have 10 rows. We didn't get JAMES and RUSSELL records. This is because the DEPARTMENTID, in Employees table is NULL for these two employees and doesn't match with ID column in Departments table.
So, in summary, INNER JOIN, returns only the matching rows between both the tables. Non matching rows are eliminated.
LEFT JOIN or LEFT OUTER JOIN
Now, let's say, I want all the rows from the Employees table, including JAMES and RUSSELL records. I want the output, as shown below.
SELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeLEFT OUTER JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeLEFT JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeLEFT JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
Note: You can use, LEFT JOIN or LEFT OUTER JOIN. OUTER keyowrd is optional
LEFT JOIN, returns all the matching rows + non matching rows from the left table. In reality, INNER JOIN and LEFT JOIN are extensively used.
RIGHT JOIN or RIGHT OUTER JOIN
I want, all the rows from the right table. The query output should be, as shown below.
SELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeRIGHT OUTER JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeRIGHT JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeRIGHT JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
Note: You can use, RIGHT JOIN or RIGHT OUTER JOIN. OUTER keyowrd is optional
RIGHT JOIN, returns all the matching rows + non matching rows from the right table.
FULL JOIN or FULL OUTER JOIN
I want all the rows from both the tables involved in the join. The query output should be, as shown below.
SELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeFULL OUTER JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeFULL JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
ORSELECT Name, Gender, Salary, DepartmentNameFROM tblEmployeeFULL JOIN tblDepartmentON tblEmployee.DepartmentId = tblDepartment.Id
Note: You can use, FULLJOIN or FULL OUTER JOIN. OUTER keyowrd is optional
FULL JOIN, returns all rows from both the left and right tables, including the non matching rows.
Joins Summary:
